using System;
using System.Collections.Generic;
using System.Drawing;
using FarPoint.Win.Spread;
using Spread.Common.DataStore;

namespace Spread.Filtering.Features
{
    public partial class CustomFilters : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Fields
        private AccessDataSource _dataSource;
        #endregion

        #region ctor
        public CustomFilters()
        {
            InitializeComponent();
            Name = "Custom Filters";
            _dataSource = new DataService().AccessDataSource;

            InitSpreadSheet(fpSpread.ActiveSheet);
            InitControls();
        }
        #endregion

        #region Private methods
        private void InitSpreadSheet(SheetView sheet)
        {
            sheet.DataSource = _dataSource.GetDataFrom("fpWine.mdb", "select OrderID, CustomerID," +
                "SalesPerson, OrderDate, ShippedDate, Freight from Orders");

            sheet.Columns[2].Width = 120;
            sheet.Columns[3, 4].Width = 80;
            sheet.Columns[5].Width = 70;

            sheet.SetMultipleRowHeights(0, sheet.RowCount, 20);

            Random random = new Random();
            var colors = Enum.GetValues(typeof(KnownColor));
            for (int row = random.Next(0, 4); row < sheet.RowCount; row += random.Next(1, 5))
            {
                sheet.Cells[row, 1].BackColor = Color.FromKnownColor((KnownColor)(colors.GetValue(
                    random.Next(27, colors.GetUpperBound(0) + 1))));
            }
            sheet.AutoFilter = true;

            HideRowFilter hideRowFilter = new HideRowFilter(sheet);
            for (int col = 0; col < sheet.ColumnCount; ++col)
            {
                hideRowFilter.ColumnDefinitions.Add(new FilterColumnDefinition(col, FilterListBehavior.Custom));
                sheet.Columns[col].Tag = sheet.Columns[col].Label;
            }
            sheet.RowFilter = hideRowFilter;
            sheet.RowFilter.ShowFilterIndicator = false;
        }

        private void InitControls()
        {
            foreach (DayOfWeek day in Enum.GetValues(typeof(DayOfWeek)))
                checkedListBox.Items.Add(day, false);

            comboDateCol.Items.AddRange(new object[] { "OrderDate", "ShippedDate" });
            comboDateCol.SelectedIndexChanged += ComboDateCol_SelectedIndexChanged;

            btnApply.Click += BtnApply_Click;
            btnReset.Click += BtnReset_Click;
        }

        private void ResetFilter(int colIndex)
        {
            fpSpread.ActiveSheet.AutoFilterReset(colIndex);

            for (int i = 0; i < checkedListBox.Items.Count; ++i)
            {
                checkedListBox.SetItemChecked(i, false);
            }
        }
        #endregion

        #region Event handlers
        private void ComboDateCol_SelectedIndexChanged(object sender, EventArgs e)
        {
            ResetFilter(3 + (comboDateCol.SelectedIndex + 1) % 2);
            btnApply.Enabled = (comboDateCol.SelectedIndex != -1);
        }

        private void BtnApply_Click(object sender, EventArgs e)
        {
            SheetView sheet = fpSpread.ActiveSheet;
            int colIndex = sheet.Columns[comboDateCol.SelectedItem.ToString()].Index;

            List<DayOfWeek> days = new List<DayOfWeek>();
            for (int i = 0; i < checkedListBox.CheckedItems.Count; ++i)
                days.Add((DayOfWeek)checkedListBox.CheckedItems[i]);

            var fcd = sheet.RowFilter.GetFilterColumnDefinition(colIndex);
            fcd.Filters.Clear();
            DayCustomFilter filter = new DayCustomFilter(sheet, days.ToArray());
            fcd.Filters.Add(filter);

            sheet.AutoFilterColumn(colIndex, filter.DisplayName, 0);

            btnReset.Enabled = true;
        }

        private void BtnReset_Click(object sender, EventArgs e)
        {
            SheetView sheet = fpSpread.ActiveSheet;
            int colIndex = sheet.Columns[comboDateCol.SelectedItem.ToString()].Index;
            ResetFilter(colIndex);

            btnReset.Enabled = false;
        }
        #endregion 
    }
}

//
//CustomFilters.Designer.cs
//


namespace Spread.Filtering.Features
{
    partial class CustomFilters
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread();
            this.fpSpread_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.btnReset = new System.Windows.Forms.Button();
            this.btnApply = new System.Windows.Forms.Button();
            this.checkedListBox = new System.Windows.Forms.CheckedListBox();
            this.comboDateCol = new System.Windows.Forms.ComboBox();
            this.label1 = new System.Windows.Forms.Label();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).BeginInit();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this.groupBox1);
            // 
            // fpSpread
            // 
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(477, 487);
            this.fpSpread.Skin = FarPoint.Win.Spread.DefaultSpreadSkins.Office2016White;
            this.fpSpread.StatusBarVisible = true;
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread_Sheet1
            // 
            this.fpSpread_Sheet1.Reset();
            this.fpSpread_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.btnReset);
            this.groupBox1.Controls.Add(this.btnApply);
            this.groupBox1.Controls.Add(this.checkedListBox);
            this.groupBox1.Controls.Add(this.comboDateCol);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Dock = System.Windows.Forms.DockStyle.Top;
            this.groupBox1.Location = new System.Drawing.Point(0, 0);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(157, 284);
            this.groupBox1.TabIndex = 0;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "DayOfWeek Filter";
            // 
            // btnReset
            // 
            this.btnReset.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.btnReset.Enabled = false;
            this.btnReset.Location = new System.Drawing.Point(42, 218);
            this.btnReset.Name = "btnReset";
            this.btnReset.Size = new System.Drawing.Size(81, 23);
            this.btnReset.TabIndex = 4;
            this.btnReset.Text = "Reset";
            this.btnReset.UseVisualStyleBackColor = true;
            // 
            // btnApply
            // 
            this.btnApply.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.btnApply.Enabled = false;
            this.btnApply.Location = new System.Drawing.Point(42, 187);
            this.btnApply.Name = "btnApply";
            this.btnApply.Size = new System.Drawing.Size(81, 23);
            this.btnApply.TabIndex = 3;
            this.btnApply.Text = "Apply";
            this.btnApply.UseVisualStyleBackColor = true;
            // 
            // checkedListBox
            // 
            this.checkedListBox.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.checkedListBox.CheckOnClick = true;
            this.checkedListBox.FormattingEnabled = true;
            this.checkedListBox.Location = new System.Drawing.Point(9, 60);
            this.checkedListBox.Name = "checkedListBox";
            this.checkedListBox.Size = new System.Drawing.Size(142, 109);
            this.checkedListBox.TabIndex = 2;
            // 
            // comboDateCol
            // 
            this.comboDateCol.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left) 
            | System.Windows.Forms.AnchorStyles.Right)));
            this.comboDateCol.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.comboDateCol.FormattingEnabled = true;
            this.comboDateCol.Location = new System.Drawing.Point(57, 23);
            this.comboDateCol.Name = "comboDateCol";
            this.comboDateCol.Size = new System.Drawing.Size(94, 21);
            this.comboDateCol.TabIndex = 1;
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(6, 26);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(45, 13);
            this.label1.TabIndex = 0;
            this.label1.Text = "Column:";
            // 
            // CustomFilters
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "CustomFilters";
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread_Sheet1)).EndInit();
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread_Sheet1;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.Button btnApply;
        private System.Windows.Forms.CheckedListBox checkedListBox;
        private System.Windows.Forms.ComboBox comboDateCol;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Button btnReset;
    }
}


//
//DayCustomFilter.cs
//


using FarPoint.Win.Spread;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;

namespace Spread.Filtering
{
    public class DayCustomFilter : BaseFilterItem
    {
        private List<DayOfWeek> _days;
        private SheetView _sheet;

        public DayCustomFilter(SheetView sheet, params DayOfWeek[] days)
        {
            SheetView = sheet;
            _days = days.ToList();
        }

        public override SheetView SheetView
        {
            set
            {
                _sheet = value;
            }
        }

        public override string DisplayName => "DayFilter";

        public override int[] Filter(int columnIndex)
        {
            List<int> filteredRows = new List<int>();
            for (int row = 0; row < _sheet.RowCount; ++row)
            {
                object value = _sheet.Cells[row, columnIndex].Value;

                if (value == null)
                    continue;

                if (value.GetType() == typeof(DateTime))
                {
                    DateTime date = Convert.ToDateTime(value);
                    if (IsFilteredIn(date))
                        filteredRows.Add(row);
                    continue;
                }

                TypeConverter converter = TypeDescriptor.GetConverter(typeof(DateTime));
                if (converter.CanConvertFrom(value.GetType()))
                {
                    DateTime date = (DateTime)converter.ConvertFrom(value);
                    if (IsFilteredIn(date))
                        filteredRows.Add(row);
                }
            }

            return filteredRows.ToArray();
        }

        public bool IsFilteredIn(DateTime value)
        {
            if (_days.Contains(value.DayOfWeek))
                return true;
            return false;
        }

        public override bool ShowInDropDown(int columnIndex, int[] filteredInRowList)
        {
            if (filteredInRowList == null)
            {
                return true;
            }
            int i = 0;
            for (i = 0; i <= filteredInRowList.Length - 1; i++)
            {
                int row = filteredInRowList[i];
                object value = _sheet.GetValue(row, columnIndex);
                if (value != null)
                {
                    if (IsFilteredIn((DateTime)value))
                    {
                        return true;
                    }
                }
            }
            return false;
        }
    }
}
